package Dao.DaoImpl;

import Dao.StudentDao;
import Pojo.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by MI on 2021/7/6.
 */
public class StudentDaoImpl implements StudentDao {
    /**
     * 公共部分
     * 将连接对象放在外边
     * 同加载驱动
     */
    private static Connection conn = null;
    public StudentDaoImpl() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_javaee2104?useUnicode=true&characterEncoding=utf-8", "root", "root");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        }

    }

    /**
     * 根据用户名查询用户信息
     * @param username 用户名
     * @return student 用户对象
     */
    @Override
    public Student findUserByName(String username) {
        String sql = "select * from table_studentuser where username=?";
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,username);
            rs=ps.executeQuery();
            Student s = new Student();
            while(rs.next()){
                s.setId(rs.getInt("id"));
                s.setName(rs.getString("username"));
                s.setPassword(rs.getString("password"));
                s.setGender(rs.getString("gender"));
                s.setAge(rs.getInt("age"));
                s.setAddress(rs.getString("address"));
                s.setGroup(rs.getInt("usergroup"));
                s.setClassname(rs.getString("classname"));
            }
            return s;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println(e.getMessage());
            }
        }
        return null;
    }

    /**
     * 注册用户信息
     * @param s 用户对象
     * @return  影响的行数
     */
    @Override
    public int InsertNewUser(Student s) {
        String sql="insert into table_studentuser(username,password,gender,age,address,usergroup,classname) values (?,?,?,?,?,?,?) ";
        PreparedStatement ps = null;

        try {
            ps= conn.prepareStatement(sql);
            ps.setObject(1,s.getName());
            ps.setObject(2,s.getPassword());
            ps.setObject(3,s.getGender());
            ps.setObject(4,s.getAge());
            ps.setObject(5,s.getAddress());
            ps.setObject(6,s.getGroup());
            ps.setObject(7,s.getClassname());
            int count = ps.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 展示所有用户信息
     * @return
     */
    @Override
    public List<Student> findAllUser() {
        String sql = "select * from table_studentuser";
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            List list = new ArrayList();
            while (rs.next()){
                Student s = new Student();
                s.setId(rs.getInt("id"));
                s.setName(rs.getString("username"));
                s.setPassword(rs.getString("password"));
                s.setGender(rs.getString("gender"));
                s.setAge(rs.getInt("age"));
                s.setAddress(rs.getString("address"));
                s.setGroup(rs.getInt("usergroup"));
                s.setClassname(rs.getString("classname"));
                list.add(s);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        }finally {
            try {
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 根据id删除用户
     * @param id
     * @return flag
     */
    @Override
    public Integer deleteUserById(Integer id) {
        String sql ="delete from table_studentuser where id =?";
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);
            id=Integer.parseInt(id.toString());
            ps.setObject(1,id);
            int count = ps.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 修改用户
     * @param s
     * @return flag
     */
    @Override
    public Integer updateUserById(Student s) {
        String sql = "update table_studentuser set username=?,password=?,gender=?,age=?,address=?,usergroup=?,classname=? where id = ?";
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,s.getName());
            ps.setObject(2,s.getPassword());
            ps.setObject(3,s.getGender());
            ps.setObject(4,Integer.parseInt((s.getAge()).toString()));
            ps.setObject(5,s.getAddress());
            ps.setObject(6,Integer.parseInt((s.getGroup()).toString()));
            ps.setObject(7,s.getClassname());
            ps.setObject(8,Integer.parseInt((s.getId()).toString()));
            int count = ps.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 根据id查询用户
     * @param id 学号
     * @return 对象
     */
    @Override
    public Student findUserById(Integer id) {

        String sql = "select * from table_studentuser where id=?";
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,Integer.parseInt(id.toString()));
            rs = ps.executeQuery();
            Student s =new Student();
            while (rs.next()){
                s.setId(Integer.valueOf(rs.getInt("id")));
                s.setName(rs.getString("username"));
                s.setPassword(rs.getString("password"));
                s.setGender(rs.getString("gender"));
                s.setAge(Integer.valueOf(rs.getInt("age")));
                s.setAddress(rs.getString("address"));
                s.setGroup(Integer.valueOf(rs.getInt("usergroup")));
                s.setClassname(rs.getString("classname"));
            }
            return s;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
